const express = require('express')
const router = express.Router()
const SQLConnect = require('./SQLconnect')
const jwt = require('jsonwebtoken')
const config = require('./config')
const multer= require('multer')
const  fs=require('fs')
// 登录
router.post('/login', (req, res) => {
    const username = req.body.username
    const password = req.body.password
    const sql = 'select * from user where  username=? and password= ? '
    var arr = [username, password]
    SQLConnect(sql, arr, result => {
        if (result.length > 0) {
            const token = jwt.sign({username: username}, config.jwtSecret, {expiresIn: '1h'})
            res.send({
                status: 200,
                token: token,
                msg: '登录成功',
            })
        } else {
            res.send({
                status: 500,
                msg: '用户名或密码错误'
            })
        }
    })

})

// 获取角色管理列表
router.get('/roleManage/list', (req, res) => {
    const sql = 'select * from role_m '
    SQLConnect(sql, null, result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                data: result
            })
        } else {
            res.send({
                status: 500,
                msg: '暂无数据'
            })
        }
    })
})

// 添加角色
router.get('/roleManage/add', (req, res) => {
    const roleName = req.query.roleName
    const sql = 'insert into role_m values (null ,?)'
    const arr = [roleName]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '添加数据成功'
            })
        } else {
            res.send({
                status: 500,
                msg: '添加数据失败'
            })
        }
    })
})

// 修改角色
router.get('/roleManage/update', (req, res) => {
    const roleName = req.query.roleName
    const roleId = req.query.roleId
    const sql = 'update role_m set roleName=? where roleId = ?'
    const arr = [roleName, roleId]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '修改成功'
            })
        } else {
            res.send({
                status: 500,
                msg: '修改失败'
            })
        }
    })
})

// 删除角色
router.get('/roleManage/delete', (req, res) => {
    const roleId = req.query.roleId
    const sql = 'delete from role_m where roleId=? '
    const arr = [roleId]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msgL: '删除成功'
            })
        } else {
            res.send({
                status: 500,
                msg: '删除失败'
            })
        }
    })
})

// 获取账户列表
router.get('/accountManage/list', (req, res) => {
    const page = req.query.page || 1
    const sql = 'select * from account_m  order by id desc limit 5 offset ' + (page - 1) * 5
    SQLConnect(sql, null, result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                data: result,
                pageSize: 5
            })
        } else {
            res.send({
                status: 500,
                msg: '暂无数据'
            })
        }
    })
})
// 查询账户条数
router.get('/accountManage/count', (req, res) => {
    const sql = 'select count(*) from account_m where id '
    SQLConnect(sql, null, result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                result
            })
        } else {
            res.send({
                status: 500,
                msg: '暂无数据'
            })
        }
    })
})
// 模糊查询
router.get('/accountManage/search', (req, res) => {
    const search = req.query.search
    const sql = "select * from account_m where concat(`name`,`role`,`phone`) like '%" + search + "%'"
    SQLConnect(sql, null, result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                result,
            })
        } else {
            res.send({
                status: 500,
                msg: '查询失败'
            })
        }
    })
})

// 添加账户
router.get('/accountManage/add', (req, res) => {
    const account = req.query.account
    const name = req.query.name
    const phone = req.query.phone
    const photo = req.query.photo
    const role = req.query.role
    const sql = "insert into account_m values(null ,?,?,?,?,?) "
    const arr = [account, name, phone, photo, role]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '添加成功'
            })
        } else {
            res.send({
                status: 500,
                msg: '添加失败'
            })
        }
    })
})

// 更新账户
router.get('/accountManage/update', (req, res) => {
    const id = req.query.id
    const account = req.query.account
    const name = req.query.name
    const phone = req.query.phone
    const photo = req.query.photo
    const role = req.query.role
    const sql = "update account_m set account=?,name=?,phone=?,photo=?,role=? where id=? "
    const arr = [account, name, phone, photo, role, id]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '修改成功'
            })
        } else {
            res.send({
                status: 500,
                msg: '修改失败'
            })
        }
    })
})
// 删除账户
router.get('/accountManage/delete', (req, res) => {
    const id = req.query.id
    const sql = "delete from account_m where  id = ? "
    const arr = [id]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '删除成功'
            })
        } else {
            res.send({
                status: 500,
                msg: '删除失败'
            })
        }
    })
})
// 获取旧密码
router.get('/home/getPwd', (req, res) => {
    const sql = "select * from user "
    SQLConnect(sql, null, result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                result
            })
        } else {
            res.send({
                status: 500,
                msg: '查询失败'
            })
        }
    })
})
// 修改密码
router.get('/home/resetPwd', (req, res) => {
    const password = req.query.password
    const sql = "update user set password= ?"
    const arr = [password]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '修改密码成功'
            })
        } else {
            res.send({
                status: 500,
                msg: '修改密码失败'
            })
        }
    })
})
// 查询房间类型
router.get('/roomType/getType',(req,res)=>{
    const sql = "select * from roomtype "
    SQLConnect(sql, null, result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                data:result
            })
        } else {
            res.send({
                status: 500,
                msg: '查询失败'
            })
        }
    })
})

// 添加房间类型
router.get('/roomType/add', (req, res) => {
    const typeName = req.query.typeName
    const roomPrice = req.query.roomPrice
    const bed_number = req.query.bed_number
    const sql = "insert into roomtype values(null,?,?,?) "
    const arr = [typeName,roomPrice,bed_number]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '添加成功'
            })
        } else {
            res.send({
                status: 500,
                msg: '添加失败'
            })
        }
    })
})
// 删除房间类型
router.get('/roomType/delete', (req, res) => {
    const id = req.query.id
    const sql = "delete from roomtype where  id = ? "
    const arr = [id]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '删除成功'
            })
        } else {
            res.send({
                status: 500,
                msg: '删除失败'
            })
        }
    })
})

// 更新房间类型
router.get('/roomType/update', (req, res) => {
    const id = req.query.id
    const typeName = req.query.typeName
    const roomPrice = req.query.roomPrice
    const bed_number = req.query.bed_number
    const sql = "update roomtype set typeName=?,roomPrice=?,bed_number=? where id=? "
    const arr = [typeName, roomPrice, bed_number,id]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '修改成功'
            })
        } else {
            res.send({
                status: 500,
                msg: '修改失败'
            })
        }
    })
})

// 查询客房条数
router.get('/roomManage/count', (req, res) => {
    const sql = 'select count(*) from room where rooId '
    SQLConnect(sql, null, result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                result
            })
        } else {
            res.send({
                status: 500,
                msg: '暂无数据'
            })
        }
    })
})

// 客房管理列表
router.get('/roomManage/getRoomList',(req,res)=>{
    const page = req.query.page || 1
    const sql = 'select * from room  order by rooId desc limit 10 offset ' + (page - 1) * 10
    SQLConnect(sql, null, result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                data:result
            })
        } else {
            res.send({
                status: 500,
                msg: '查询失败'
            })
        }
    })
})

// 客房管理模糊查询
router.get('/roomManage/search', (req, res) => {
    const search = req.query.search
    const sql = "select * from room where concat(`roomStateName`,`roomTypeName`,`rooId`) like '%" + search + "%'"
    SQLConnect(sql, null, result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                result,
            })
        } else {
            res.send({
                status: 500,
                msg: '查询失败'
            })
        }
    })
})

// 添加客房管理
router.get('/roomManage/add',(req,res)=>{
    const roomTypeId = req.query.roomTypeId
    const roomStateName = req.query.roomStateName
    const roomTypeName = req.query.roomTypeName
    const price = req.query.price
    const roomNum = req.query.roomNum
    const imgUrl = req.query.imgUrl
    const sql = "insert into room values(null ,1,?,?,?,?,?,?) "
    const arr = [roomTypeId,roomStateName,roomTypeName,price,roomNum,imgUrl]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '添加成功'
            })
        } else {
            res.send({
                status: 500,
                msg: '添加失败'
            })
        }
    })
})

// 更新客房管理
router.get('/roomManage/update', (req, res) => {
    const rooId = req.query.rooId
    const roomTypeId = req.query.roomTypeId
    const roomStateName = req.query.roomStateName
    const roomTypeName = req.query.roomTypeName
    const price = req.query.price
    const roomNum = req.query.roomNum
    const sql = "update room set roomTypeId=?,roomStateName=?,roomTypeName=?,price=?,roomNum=? where rooId=? "
    const arr = [roomTypeId,roomStateName,roomTypeName,price,roomNum,rooId]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '修改成功'
            })
        } else {
            res.send({
                status: 500,
                msg: '修改失败'
            })
        }
    })
})

// 删除客房
router.get('/roomManage/delete', (req, res) => {
    const rooId = req.query.rooId
    const sql = "delete from room where  rooId = ? "
    const arr = [rooId]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '删除成功'
            })
        } else {
            res.send({
                status: 500,
                msg: '删除失败'
            })
        }
    })
})

// 上传图片
var storage = multer.diskStorage({
    // destination指定上传路径
    destination: function (req, file, cb) {
        cb(null, './upload')
    },
    // filename上传文件名称
    filename: function (req, file, cb) {
        cb(null, Date.now() + '-' + file.originalname)
    }

})
var createFolder = function (folder) {
    try {
        fs.accessSync(folder)
    } catch (e) {
        return e
    }
}
var uploadFolder = './upload'
createFolder(uploadFolder)
var upload = multer({storage: storage})
router.post('/upload', upload.single('file'), function (req, res, next) {
    var file = req.file
    // console.log('文件类型: %s',file.mimetype)
    // console.log('原始文件名称: %s',file.originalname)
    // console.log('文件大小: %s',file.size)
    // console.log('文件路径: %s',file.path)
    res.json({res_code: '0', name: file.originalname, url: file.path})
})

//根据ID查询图片
router.get('/room/img',(req,res)=>{
    const rooId=req.query.rooId
    const  sql="select imgUrl from room where rooId =? "
    var arr=[rooId]
    SQLConnect(sql,arr,result=>{
        if (result.length>0){
            res.send({
                status:200,
                data:result
            })
        }else {
            res.send({
                status:500,
                msg:'暂无数据'
            })
        }
    })
})

// 客房管理列表条数
router.get('/guestManage/count', (req, res) => {
    const sql = 'select count(*) from guest_m where guestId '
    SQLConnect(sql, null, result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                result
            })
        } else {
            res.send({
                status: 500,
                msg: '暂无数据'
            })
        }
    })
})

// 顾客管理列表
router.get('/guestManage/getGuestList',(req,res)=>{
    const page = req.query.page || 1
    const sql = 'select * from guest_m  order by guestId desc limit 10 offset ' + (page - 1) * 10
    SQLConnect(sql, null, result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                data:result
            })
        } else {
            res.send({
                status: 500,
                msg: '查询失败'
            })
        }
    })
})

// 添加顾客管理
router.get('/guestManage/add',(req,res)=>{
    const indentityId = req.query.indentityId
    const guestName = req.query.guestName
    const phone = req.query.phone
    const roomId = req.query.roomId
    const resideDate = req.query.resideDate
    const deposit = req.query.deposit
    const roomType = req.query.roomType
    const stateR = req.query.stateR
    const roomState=req.query.roomState
    const sql = "insert into guest_m values(null ,?,?,?,?,?,?,?,?,?) "
    const arr=[indentityId,guestName,phone,roomId,resideDate,deposit,roomType,stateR,roomState ]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '添加成功'
            })
        } else {
            res.send({
                status: 500,
                msg: '添加失败'
            })
        }
    })
})

// 更新顾客管理
router.get('/guestManage/update', (req, res) => {
    const guestId=req.query.guestId
    const indentityId = req.query.indentityId
    const guestName = req.query.guestName
    const phone = req.query.phone
    const roomId = req.query.roomId
    const resideDate = req.query.resideDate
    const deposit = req.query.deposit
    const roomType = req.query.roomType
    const stateR = req.query.stateR
    const roomState=req.query.roomState
    const sql = "update guest_m set indentityId=?,guestName=?,phone=?,roomId=?,resideDate=?,deposit=?,roomType=?,stateR=?,roomState=? where guestId=? "
    const arr = [indentityId,guestName,phone,roomId,resideDate,deposit,roomType,stateR,roomState,guestId]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '修改成功'
            })
        } else {
            res.send({
                status: 500,
                msg: '修改失败'
            })
        }
    })
})

// 客房管理模糊查询
router.get('/guestManage/search', (req, res) => {
    const search = req.query.search
    const sql = "select * from guest_m where concat(`guestName`,`stateR`,`roomId`,`roomType`) like '%" + search + "%'"
    SQLConnect(sql, null, result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                result,
            })
        } else {
            res.send({
                status: 500,
                msg: '查询失败'
            })
        }
    })
})

// 删除顾客
router.get('/guestManage/delete', (req, res) => {
    const guestId = req.query.guestId
    const sql = "delete from guest_m where  guestId = ? "
    const arr = [guestId]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '删除成功'
            })
        } else {
            res.send({
                status: 500,
                msg: '删除失败'
            })
        }
    })
})

//结账
router.get('/guestManage/checkMoney',(req,res)=>{
    const guestId = req.query.guestId
    const  sql="update guest_m set stateR='结账',roomState='1' where guestId=? "
    const arr=[guestId]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '结账成功',
            })
        } else {
            res.send({
                status: 500,
                msg: '结账失败',
            })
        }
    })
})

// 消息
router.get('/message/receiveM',(req,res)=>{
    const sql="select * from message "
    SQLConnect(sql,null,result=>{
        if (result.length > 0) {
            res.send({
                status: 200,
                msg: '查询成功',
                data:result
            })
        } else {
            res.send({
                status: 500,
                msg: '暂无数据',
            })
        }
    })
})

// 发布消息
router.get('/message/add',(req,res)=>{
    const message=req.query.message
    const sql = "insert into message values(null ,?) "
    const arr=[message]
    SQLConnect(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: '发布成功',
                data:result
            })
        } else {
            res.send({
                status: 500,
                msg: '发布失败'
            })
        }
    })
})

// 获取邮箱消息
router.get('/message/email',(req,res)=>{
     const sql="select * from message "
     SQLConnect(sql,null,result=>{
         if (result.length>0){
             res.send({
                 status: 200,
                 msg: '获取邮箱消息成功',
                 data:result
             })
         } else {
             res.send({
                 status: 500,
                 msg: '暂无数据'
             })
         }
     })
})

// 删除邮箱消息
router.get('/message/delete',(req,res)=>{
    const id=req.query.id
    const sql="delete from message where id=? "
    const arr=[id]
    SQLConnect(sql,arr,result=>{
        if (result.affectedRows>0){
            res.send({
                status:200,
                msg:'删除成功'
            })
        }else {
            res.send({
                status:500,
                msg:'删除失败'
            })
        }
    })
})
module.exports = router
